import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpl implements UserDAO {
    @Override
    public User findByAccountAndPIN(String account, String PIN) {
        try (Connection connection = DBUtil.getConnection()) {
            String sql = "SELECT * FROM User WHERE Account =? AND PIN =?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.setString(2, PIN);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                User user = new User();
                user.setAccount(resultSet.getString("Account"));
                user.setPIN(resultSet.getString("PIN"));
                user.setUtype(resultSet.getString("Utype"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        try (Connection connection = DBUtil.getConnection()) {
            String sql = "SELECT * FROM User";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setAccount(resultSet.getString("Account"));
                user.setPIN(resultSet.getString("PIN"));
                user.setUtype(resultSet.getString("Utype"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    @Override
    public void save(User user) {
        try (Connection connection = DBUtil.getConnection()) {
            String sql = "INSERT INTO User (Account, PIN, Utype) VALUES (?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getAccount());
            preparedStatement.setString(2, user.getPIN());
            preparedStatement.setString(3, user.getUtype());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Override
    public void deleteUser(String account) {
        try (Connection connection = DBUtil.getConnection()) {
            // 先删除 manager 表中的相关记录
            String sql = "DELETE FROM manager WHERE account = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.executeUpdate();

            // 删除 Result 表中的相关记录
            sql = "DELETE FROM Result WHERE account = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.executeUpdate();

            // 删除 Testuser 表中的相关记录
            sql = "DELETE FROM Testuser WHERE account = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.executeUpdate();

            // 最后删除 User 表中的记录
            sql = "DELETE FROM User WHERE Account = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void resetUserPassword(String account, String newPassword) {
        try (Connection connection = DBUtil.getConnection()) {
            // 先检查账号是否存在
            String checkSql = "SELECT * FROM User WHERE Account = ?";
            PreparedStatement checkStatement = connection.prepareStatement(checkSql);
            checkStatement.setString(1, account);
            ResultSet resultSet = checkStatement.executeQuery();
            if (resultSet.next()) {
                // 账号存在，执行更新操作
                String sql = "UPDATE User SET PIN = ? WHERE Account = ?";
                PreparedStatement updateStatement = connection.prepareStatement(sql);
                updateStatement.setString(1, newPassword);
                updateStatement.setString(2, account);
                updateStatement.executeUpdate();
            } else {
                System.err.println("未找到对应的用户账号，密码更新失败。");
            }
        } catch (SQLException e) {
            System.err.println("密码更新时发生 SQL 错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public List<User> getUsersByType(String userType) {
        List<User> users = new ArrayList<>();
        try (Connection connection = DBUtil.getConnection()) {
            String sql = "SELECT * FROM User WHERE Utype =?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, userType);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setAccount(resultSet.getString("Account"));
                user.setPIN(resultSet.getString("PIN"));
                user.setUtype(resultSet.getString("Utype"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
}